Excel VBA vs Python xlwings对照学习：在Excel VBA中调用Python

使用xlwings加载项，可以帮助我们在Excel VBA中调用Python。使用它之前，需要先进行安装。[大谦Excel，dqexcel点com]

xlwings加载项

完成xlwings包的安装之后，在DOS命令窗口键入下面的命令行可以直接安装xlwings加载项。

xlwings addin install

安装完成后，Excel主界面上会添加xlwings选项卡，设置选项卡上的选项，可以完成混合编程前的配置工作。

这是一种安装方法，如果这种方法失败，也可以直接加载宏文件。安装 xlwings 之后，xlwings 包会在Python安装路径的Lib\site-packages\xlwings\addin目录下放置一个xlwings.xlsm 的 Excel宏文件，可以直接加载它。按照以下步骤进行：

• 加载“开发工具”选项卡，请参见10.1.1小节内容。

• 在“开发工具”功能区单击“Excel加载项”按钮，打开“加载宏”对话框，如图10-4所示。

图10-4 加载xlwings宏

• 单击“浏览…”按钮找到Python安装路径的Lib\site-packages\xlwings\addin目录下的xlwings.xlsm文件，确定。

• 单击“确定”按钮。Excel主界面上添加xlwings选项卡，如图10-5所示。

图10-5 xlwings加载项

xlwings选项卡中各选项的功能说明如下：

• Interpreter: 指定Python解释器的路径。输入python或pythonw，也可以输入可执行文件的完整路径，如"C:\Python37\pythonw.exe"。如果使用的是Anaconda，使用下面的Conda Base和Conda Env。如果留空，解释器设置为pythonw。

• PYTHONPATH: 指定Python源文件的路径，如果.py文件在D盘下，输入路径为"D:"。注意最后不要添加反斜杠，即输入"D:\"会导致出错。

• Conda Base: 如果使用的是Windows并使用conda env，在此处键入Anaconda或Miniconda安装的路径和名称，例如： "C:\Users\Username\Miniconda3"或"%USERPROFILE%\Anaconda"。注意，至少需要conda 4.6。

• Conda Env: 如果使用的是Windows并使用conda env，在此输入conda env的名称，例如：myenv。注意，这要求将Interpreter留空或将其设置为python或pythonw。

• UDF Modules: 用于下节介绍的自定义函数(UDF)的设置。指定导入UDF的Python模块的名称（没有.py扩展名）。用";"分隔多个模块。示例：UDF_MODULES ="common_udfs; myproject"默认导入与Excel电子表格相同的目录中的文件，该文件具有相同的名称，但以.py结尾。如果留空，需要xlsm文件与.py文件的名称相同且在同一目录下；如果不同，则需要输入文件名（不需要py后缀），并将py文件放入PYTHONPATH所在文件夹内。

• Debug UDFs: 选择此项时，手动运行xlwings COM服务器进行调试。

• Import Functions: 第1次使用，或者在.py文件更新后单击此按钮导入它。

• RunPython: Use UDF Server: 选择它，对于RunPython使用与UDF相同的COM服务器。这样做速度更快，因为解释器在每次调用后都不会关闭。

• Restart UDF Server: 单击它会关闭UDF Server / Python解释器。它将在下一个函数调用时重新启动。

编写Python文件

设置相关选项后，编写Python文件。可以在Python IDLE的脚本编辑器中编写，也可以用记事本编写，编写完成以后保存为py文件。这里我们试图用Matplotlib根据给定的数据绘制堆栈面积图，绘完以后将图形添加到Excel工作表中的指定位置。该py文件在下载资料包中的Samples目录下的ch22\ vba-python子目录下可以找到，文件名为plt.py。测试时可以将它跟同目录下的Excel宏文件xw-test.xlsm一起复制到D盘下。

code.python

import xlwings as xw   #导入xlwings包
import matplotlib.pyplot as plt   #导入Matplotlib包
def pltplot():   #定义函数绘图
   bk=xw.Book.caller()   #获取工作簿
   sht=bk.sheets[0]   #获取工作表
   fig=plt.figure()    #新建绘图窗口
   x=[1,2,3,4,5]   #绘图数据
   y1=[2,1,4,3,5]
   y2=[0,2,1,6,4]
   y3=[1,4,5,8,6]
   plt.stackplot(x, y1, y2, y3)    #利用获取的数据绘堆栈面积图
   #将创建的图形添加到工作表指定位置
   sht.pictures.add(fig,name="plt_test",left=20,top=140,width=250,height=160)

在Excel VBA中调用Python

新建一个Excel工作簿，保存为xw-test.xlsm，为启用宏的Excel工作簿文件。该文件在下载资料包中的Samples目录下的ch22\ vba-python子目录下可以找到。测试时可以将它跟同目录下的Python文件plt.py一起复制到D盘下。

在Excel主界面中单击“开发工具”选项卡，单击功能区的Visual Basic按钮，打开Excel VBA编程环境。在“工具”菜单中单击“引用…”选项，打开“引用”对话框，如图10-6所示。在对话框上单击“浏览…”按钮，在右下角将扩展名设置为任意文件，找到Python安装路径的Lib\site-packages\xlwings\addin目录下的xlwings.xlsm文件，引用它。

图10-6 引用xlwings插件

在“插入”菜单中单击“模块”选项，添加一个模块。在模块的代码编辑器中输入下面的代码，用RunPython函数运行10.2.2小节创建的plt.py文件中的pltplot函数，使用之前需要用import命令导入该模块。

code.vba

Sub plttest()
  RunPython "import plt;plt.pltplot()"
End Sub

运行该过程，绘制堆栈面积图并添加到工作表中，如图10-7所示。

图10-7 VBA调用Python代码绘制堆栈面积图

xlwings加载项使用避坑指南

使用xlwings加载项时操作并不难，有时最难的是在安装阶段出现问题。下面就笔者在使用过程中遇到的坑作一些说明。

一、"文件未找到：xlwings32-0.4.4.dll"错误

出现该错误是因为xlwings的安装有问题，需要重新安装，其中的版本号根据具体情况有差异。在DOS命令窗口使用python –m pip install xlwings命令安装时一般不会出现错误，笔者触发该错误是在下载老版本的xlwings包并用setup.py手动安装时出现的。此时要避免手动安装，使用接下来介绍的方法安装老版本。

二、"could not activate Python COM server"错误

笔者发现xlwings加载项对xlwings的版本比较敏感，使用某个老版本时没有问题，升级到新版本后就不能正常工作了，并提示类似"could not activate Python COM server"的错误。比如笔者使用0.10.1版本时出现上面的错误，使用0.4.4版本时正确。

此时关闭所有Excel文件，在DOS命令窗口用python –m pip uninstall xlwings命令卸载xlwings，然后安装老版本。安装老版本的xlwings，在安装时指定版本号，例如，安装0.16.4版本的xlwings，在DOS命令窗口输入：

python –m pip install xlwings==0.16.4

三、"Python process exited before…"错误

该错误提示的完整内容类似于"Python process exited before it was possible to create the interface object. Command: pythonw.exe -c ""import sys;sys.path.append(r'D:\SkyDrive\APP\VDI\Project Journal');import xlwings.server; xlwings.server.serve('{4c3ae7ba-2be9-4782-a377-f13934ffc4a9}')"。出现这个错误，是在xlwings功能区设置PYTHONPATH参数的值时，在最后加了反斜杠，如"D:"是对的，"D:\"是错的，此时编译时会因为语法错误导致失败。[大谦Excel，dqexcel点com]